package model.db;

import java.text.DecimalFormat;
import java.util.LinkedList;
import javax.swing.table.DefaultTableModel;
import model.entity.TweetLocation;
import model.entity.TwitterUser;
import org.json.JSONArray;
import org.json.JSONObject;



public class TwitterDB  extends DatabaseHelper {
	public static final String URL ="jdbc:mysql://"+"localhost"+":3306/tweet_stat";
	public static final String CLASSPATH ="com.mysql.jdbc.Driver";
	public static final String USER = "root";
	public static final String PASSWORD = "rajin";
	
	public TwitterDB()
	{
		super(TwitterDB.URL,TwitterDB.CLASSPATH, TwitterDB.USER, TwitterDB.PASSWORD);
	}
	
	public JSONArray getTopUsersInLocation(String location, String fromDate, String toDate, String user, String topic)
        {
            String locationPart  ="";
            if(!location.equals(""))
            {
                locationPart ="AND UPPER(u.location) LIKE '%"+location.trim().toUpperCase()+"%' "; 
            }
            String datePart = " ";
            if(!(fromDate.trim().equals("") && toDate.trim().equals("")))
            {
                datePart = "AND t.creation_date_time BETWEEN STR_TO_DATE('"+fromDate+"', '%m/%d/%Y') AND STR_TO_DATE('"+toDate+"', '%m/%d/%Y') ";
            }
            String userPart ="";
            if(!user.equals(""))
            {
                userPart ="AND UPPER(u.user_name) LIKE '%"+user.trim().toUpperCase()+"%' "; 
            }   
            String topicPart = "";
            if(!topic.equals(""))
            {
                topicPart ="AND UPPER(tp.topic_text) LIKE '%"+topic.trim().toUpperCase()+"%' "; 
            }
            
            String command = "SELECT u.user_id,u.user_name, u.screen_name,"
                            +"u.description,u.profile_image_url,u.location, COUNT(*) as cnt "
                            +"FROM "
                            +"twitter_user AS u  "
                            +"INNER JOIN  "
                            +"tweet AS t  "
                            +"ON t.user_id = u.user_id  "
                            +"LEFT OUTER JOIN  "
                            +"tweet_topic AS tt  "
                            +"ON "
                            +"tt.tweet_id = t.tweet_id "
                            +"LEFT OUTER JOIN  "
                            +"topic AS tp "
                            +"ON tt.topic_id = tp.topic_id "
                            +"WHERE 1=1 "+
                            locationPart+
                            datePart+
                            userPart+
                            "GROUP BY  u.user_id,u.user_name, u.screen_name, "+
                            "u.description,u.profile_image_url,u.location "+
                            "ORDER BY COUNT(*) DESC "+
                            "LIMIT 10;";
            DefaultTableModel dt = this.getData(command);
            JSONArray result = new JSONArray();
            int row = dt.getRowCount();
            if(row > 0)
            {
                for(int i=0;i<row;i++)
                {
                    Long userId = (Long) Long.parseLong( dt.getValueAt(i, 0).toString());
                    String userName = (String) dt.getValueAt(i, 1);
                    String screenName = (String) dt.getValueAt(i, 2);
                    String description = (String) dt.getValueAt(i, 3);
                    String profileImageUrl = (String) dt.getValueAt(i, 4);
                    String locationVal = (String) dt.getValueAt(i, 5);
                    
                    Long count =  (Long) Long.parseLong( dt.getValueAt(i, 6).toString());
                    TwitterUser u = new TwitterUser(userId, userName, screenName, description, profileImageUrl, locationVal);
                    JSONObject obj = u.toJSON();
                    obj.put("TweetCount", count);
                    result.put(obj);
                }
            }
            return result;
        }
        
	public JSONArray getTopTopicsInLocation(String location, String fromDate, String toDate, String user, String topic)
        {
            String locationPart  ="";
            if(!location.equals(""))
            {
                locationPart ="AND UPPER(u.location) LIKE '%"+location.trim().toUpperCase()+"%' "; 
            }
            String datePart = " ";
            if(!(fromDate.trim().equals("") && toDate.trim().equals("")))
            {
                datePart = "AND t.creation_date_time BETWEEN STR_TO_DATE('"+fromDate+"', '%m/%d/%Y') AND STR_TO_DATE('"+toDate+"', '%m/%d/%Y') ";
            }
            String userPart ="";
            if(!user.equals(""))
            {
                userPart ="AND UPPER(u.user_name) LIKE '%"+user.trim().toUpperCase()+"%' "; 
            }   
            String topicPart = "";
            if(!topic.equals(""))
            {
                topicPart ="AND UPPER(tp.topic_text) LIKE '%"+topic.trim().toUpperCase()+"%' "; 
            }
            String command = "SELECT  "
                            +"tp.topic_id, tp.topic_text, COUNT(tp.topic_id) as cnt "
                            +"FROM  "
                            +"twitter_user AS u  "
                            +"INNER JOIN  "
                            +"tweet AS t  "
                            +"ON t.user_id = u.user_id  "
                            +"INNER JOIN  "
                            +"tweet_topic AS tt  "
                            +"ON "
                            +"tt.tweet_id = t.tweet_id "
                            +"INNER JOIN  "
                            +"topic AS tp "
                            +"ON tt.topic_id = tp.topic_id "
                            +"WHERE  1=1 "
                            +locationPart
                            +datePart
                            +userPart
                            +topicPart
                            +"GROUP BY tp.topic_id,tp.topic_text "
                            +"ORDER BY COUNT(tp.topic_id) DESC "
                            +"LIMIT 10";
            DefaultTableModel dt = this.getData(command);
            JSONArray result = new JSONArray();
            int row = dt.getRowCount();
            if(row > 0)
            {
                for(int i=0;i<row;i++)
                {
                    Long topicId =(Long) Long.parseLong( dt.getValueAt(i, 0).toString());
                    String t = (String) dt.getValueAt(i, 1);
                    Long count =  (Long) Long.parseLong( dt.getValueAt(i, 2).toString());
                   
                    JSONObject obj = new JSONObject();
                    obj.put("TopicId",topicId);
                    obj.put("Topic", t);
                    obj.put("Count", count);
                    result.put(obj);
                }
            }
            return result;
        }
        
        public JSONArray getTweetLocationsInLocation(String location, String fromDate, String toDate, String user, String topic)
        {
            String locationPart  ="";
            if(!location.equals(""))
            {
                locationPart ="AND UPPER(u.location) LIKE '%"+location.trim().toUpperCase()+"%' "; 
            }
            String datePart = " ";
            if(!(fromDate.trim().equals("") && toDate.trim().equals("")))
            {
                datePart = "AND t.creation_date_time BETWEEN STR_TO_DATE('"+fromDate+"', '%m/%d/%Y') AND STR_TO_DATE('"+toDate+"', '%m/%d/%Y') ";
            }
            String userPart ="";
            if(!user.equals(""))
            {
                userPart ="AND UPPER(u.user_name) LIKE '%"+user.trim().toUpperCase()+"%' "; 
            }   
            String topicPart = "";
            if(!topic.equals(""))
            {
                topicPart ="AND UPPER(tp.topic_text) LIKE '%"+topic.trim().toUpperCase()+"%' "; 
            }
            String command ="SELECT  "
                            +"t.tweet_id,u.user_id,u.screen_name,u.user_name, t.tweet_text,u.profile_image_url,t.latitude,t.longitude "
                            +"FROM  "
                            +"twitter_user AS u  "
                            +"INNER JOIN  "
                            +"tweet AS t  "
                            +"ON t.user_id = u.user_id  "
                            +"LEFT OUTER JOIN  "
                            +"tweet_topic AS tt  "
                            +"ON "
                            +"tt.tweet_id = t.tweet_id "
                            +"LEFT OUTER JOIN  "
                            +"topic AS tp "
                            +"ON tt.topic_id = tp.topic_id "
                            +"WHERE  1=1 "
                            +locationPart
                            +datePart
                            +userPart
                            +topicPart
                            +" ;";
            DefaultTableModel dt = this.getData(command);
            JSONArray result = new JSONArray();
            int row = dt.getRowCount();
            if(row > 0)
            {
                for(int i=0;i<row;i++)
                {
                    Long twitterId = (Long) Long.parseLong( dt.getValueAt(i, 0).toString());
                    Long userId = (Long) Long.parseLong( dt.getValueAt(i, 1).toString());
                    String screenName = (String) dt.getValueAt(i, 2);
                    String userName = (String) dt.getValueAt(i, 3);
                   
                    String tweetText = (String) dt.getValueAt(i, 4);
                    String profileImageUrl = (String) dt.getValueAt(i, 5);
                    Double latitude = (Double) dt.getValueAt(i, 6);
                    Double longitude = (Double) dt.getValueAt(i, 7);
                    
                    TweetLocation tweetLocation = new TweetLocation(userId, userId, userName, screenName, tweetText, profileImageUrl, latitude, longitude);
                    JSONObject obj = tweetLocation.toJSON();
                    result.put(obj);
                }
            }
            return result;
        }
        
        public JSONArray getContinentInfo(String location, String fromDate, String toDate, String user, String topic)
        {
            String locationPart  ="";
            if(!location.equals(""))
            {
                locationPart ="AND UPPER(u.location) LIKE '%"+location.trim().toUpperCase()+"%' "; 
            }
            String datePart = " ";
            if(!(fromDate.trim().equals("") && toDate.trim().equals("")))
            {
                datePart = "AND t.creation_date_time BETWEEN STR_TO_DATE('"+fromDate+"', '%m/%d/%Y') AND STR_TO_DATE('"+toDate+"', '%m/%d/%Y') ";
            }
            String userPart ="";
            if(!user.equals(""))
            {
                userPart ="AND UPPER(u.user_name) LIKE '%"+user.trim().toUpperCase()+"%' "; 
            }   
            String topicPart = "";
            if(!topic.equals(""))
            {
                topicPart ="AND UPPER(tp.topic_text) LIKE '%"+topic.trim().toUpperCase()+"%' "; 
            }
            String command = "SELECT  "
                            +"c.continent_id, c.continent_name, COUNT(t.tweet_id) AS cnt "
                            +"FROM  "
                            +"twitter_user AS u  "
                            +"INNER JOIN  "
                            +"tweet AS t  "
                            +"ON t.user_id = u.user_id  "
                            +"LEFT OUTER JOIN  "
                            +"tweet_topic AS tt  "
                            +"ON "
                            +"tt.tweet_id = t.tweet_id "
                            +"LEFT OUTER JOIN  "
                            +"topic AS tp "
                            +"ON tt.topic_id = tp.topic_id "
                            +"INNER JOIN  "
                            +"continent AS c "
                            +"ON c.continent_id = t.continent_id "
                            +"WHERE 1=1 "
                            +locationPart
                            +datePart
                            +userPart
                            +topicPart
                            +"GROUP BY  "
                            +"c.continent_id, c.continent_name "
                            +";";
             DefaultTableModel dt = this.getData(command);
            JSONArray result = new JSONArray();
            int row = dt.getRowCount();
            if(row > 0)
            {
                for(int i=0;i<row;i++)
                {
                    
                    Long continentId =  (Long) Long.parseLong( dt.getValueAt(i, 0).toString());
                    String continentName = (String) dt.getValueAt(i, 1); 
                    Long count =  (Long) Long.parseLong( dt.getValueAt(i, 2).toString());
                    
                    JSONObject obj = new JSONObject();
                    obj.put("ContinentName", continentName);
                    obj.put("Count", count);
                    result.put(obj);
                }
               
                
            }
            return result;
        }
        
         public JSONArray getWeeklyActivities(String topicIds, int continentId, String fromDate, String toDate)
        {
           
            String datePart = " ";
            if(!(fromDate.trim().equals("") && toDate.trim().equals("")))
            {
                datePart = "AND t.creation_date_time BETWEEN STR_TO_DATE('"+fromDate+"', '%m/%d/%Y') AND STR_TO_DATE('"+toDate+"', '%m/%d/%Y') ";
            }
            String command = "SELECT tp.topic_id,tp.topic_text, WEEKDAY(t.creation_date_time) as week_id, COUNT(tp.topic_id) AS Cnt "
                            +" FROM  "
                            +"tweet AS t  "
                            +"INNER JOIN  "
                            +"tweet_topic AS tt  "
                            +"ON "
                            +"tt.tweet_id = t.tweet_id "
                            +"INNER JOIN  "
                            +"topic AS tp "
                            +"ON tt.topic_id = tp.topic_id "
                            +"WHERE  "
                            +"tp.topic_id IN ("+topicIds+") "
                            +" AND t.continent_id = "+continentId+" "
                            + datePart
                            +"GROUP BY tp.topic_id, tp.topic_text, WEEKDAY(t.creation_date_time) "
                            +"ORDER BY  tp.topic_id,WEEKDAY(t.creation_date_time) ASC "
                            +";";
            DefaultTableModel dt = this.getData(command);
            JSONArray result = new JSONArray();
            int row = dt.getRowCount();
            if(row > 0)
            {
                String lastTopic = "";
                JSONObject lastSeries = null;
                int lastIndex=0;;
                for(int i=0;i<row;i++)
                {
                    Long topicId =  (Long) Long.parseLong( dt.getValueAt(i, 0).toString());                   
                    String topicText = (String) dt.getValueAt(i, 1); 
                    Long weekId =  (Long) Long.parseLong( dt.getValueAt(i, 2).toString());
                    Long count =  (Long) Long.parseLong( dt.getValueAt(i, 3).toString());
                    
                    if(lastTopic.equalsIgnoreCase(topicText))
                    {
                        JSONArray arr = lastSeries.getJSONArray("data");
                        while(lastIndex< weekId)
                        {
                            arr.put(0);
                            lastIndex++;
                        }
                        arr.put(count);
                        lastIndex++;
                        
                    }
                    else
                    {
                        if(lastSeries != null)
                        {
                            JSONArray arr = lastSeries.getJSONArray("data");
                            while(lastIndex< 7)
                            {
                                arr.put(0);
                                lastIndex++;
                            }
                            result.put(lastSeries);
                        }
                        
                        
                        
                        lastTopic = topicText;
                        lastSeries = new JSONObject();
                        lastSeries.put("name",lastTopic);
                        JSONArray arr = new JSONArray();
                        lastIndex = 0;
                        while(lastIndex< weekId)
                        {
                            arr.put(0);
                            lastIndex++;
                        }
                        arr.put(count);
                        lastIndex++;
                        lastSeries.put("data", arr);
                    }
                }
                JSONArray arr = lastSeries.getJSONArray("data");
                while(lastIndex< 7)
                {
                    arr.put(0);
                    lastIndex++;
                }
                result.put(lastSeries);
                
            }
            return result;
        }
       
         public LinkedList<String> getTopics(String userName, String datefrom,String dateto)
        {
            String query = "select TEMP.topic_text as topic_text from "
                    + " (select distinct topic_text,count(tweet_id) as frequency  from "
                    +"twitter_user A inner join tweet_new B on A.user_id = B.user_id "
                    +"where A.user_name = '"+userName+"' "
                    +"and B.creation_date_time >= STR_TO_DATE('"+datefrom+"', '%m/%d/%Y') "
                    + "and B.creation_date_time <= STR_TO_DATE('"+dateto+"', '%m/%d/%Y') " 
                    +"group by topic_text order by frequency desc LIMIT 10) AS TEMP";
            DefaultTableModel table = this.getData(query);
            int row = table.getRowCount();
            LinkedList<String> result = new LinkedList<String>();
            for(int i=0;i< row ;i++)
            {
                String topic =(String)table.getValueAt(i,0);
                result.add(topic);
                
            }
            return result;
        }
        
        public JSONArray getuserName(String topic, String datefrom,String dateto)
        {
//            String query = "select TEMP.user_name as user_name from "
//                    +"(select distinct user_name,count(tweet_id) as frequency  from "
//                    +"twitter_user A right join tweet_new B on A.user_id = B.user_id "
//                    +"where B.topic_text = '" + topic + "' "
//                    +"and B.creation_date_time >= STR_TO_DATE('"+datefrom+"', '%m/%d/%Y') "
//                    + "and B.creation_date_time <= STR_TO_DATE('"+dateto+"', '%m/%d/%Y') " 
//                    +"group by user_name order by frequency desc LIMIT 10) AS TEMP";
            
             String query = "select TEMP.user_id,TEMP.user_name as user_name,TEMP.screen_name,TEMP.description,profile_image_url,location,frequency from "
                    +"(select distinct A.user_id,user_name,screen_name,"
                     + "description, profile_image_url,location,"
                     + "count(tweet_id) as frequency  from "
                    +"twitter_user A right join tweet_new B on A.user_id = B.user_id "
                    +"where B.topic_text = '" + topic + "' "
                    +"and B.creation_date_time >= STR_TO_DATE('"+datefrom+"', '%m/%d/%Y') "
                    + "and B.creation_date_time <= STR_TO_DATE('"+dateto+"', '%m/%d/%Y') " 
                    +"group by user_name order by frequency desc LIMIT 10) AS TEMP";
            
            DefaultTableModel dt = this.getData(query);
            JSONArray result = new JSONArray();
            int row = dt.getRowCount();
            
//            LinkedList<String> newresult = new LinkedList<String>();
            if(row>0)
            {
                for(int i=0;i< row ;i++)
                {
                    Long userId = (Long) Long.parseLong( dt.getValueAt(i, 0).toString());
                    String userName = (String) dt.getValueAt(i, 1);
                    String screenName = (String) dt.getValueAt(i, 2);
                    String description = (String) dt.getValueAt(i, 3);
                    String profileImageUrl = (String) dt.getValueAt(i, 4);
                    String locationVal = (String) dt.getValueAt(i, 5);
                    
                    Long count =  (Long) Long.parseLong( dt.getValueAt(i, 6).toString());
                    TwitterUser user = new TwitterUser(userId, userName, screenName, description, profileImageUrl, locationVal);
                    JSONObject obj = user.toJSON();
                    obj.put("TweetCount", count);
                    result.put(obj);

                }
            }
            return result;
        }
        
        
	public static void main(String []args)
	{
		System.out.println("User DB class");
	}
}
